#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
-- 插入数据到 每天/每月/每年各个校区的报名人数统计表
INSERT INTO
    edu_online_ads.time_school
SELECT
    dt_year,
    dt_month,
    dt_day,
    itcast_school_name,
    sign_up_num
FROM
    edu_online_dws.sign_up_count
WHERE
    group_type = 'school';

-- 插入数据到 每天/每月/每年线上线下各个校区的报名人数统计表
INSERT INTO
    edu_online_ads.time_ofline_school
SELECT
    dt_year,
    dt_month,
    dt_day,
    itcast_school_name,
    origin_type,
    sign_up_num
FROM
    edu_online_dws.sign_up_count
WHERE
     group_type = 'school'
  OR group_type = 'ofline';

-- 插入数据到 每天/每月/每年线上线下各个学科的报名人数统计表
INSERT INTO
    edu_online_ads.time_ofline_subject
SELECT
    dt_year,
    dt_month,
    dt_day,
    itcast_subject_name,
    origin_type,
    sign_up_num
FROM
    edu_online_dws.sign_up_count
WHERE
     group_type = 'subject'
  OR group_type = 'ofline';

-- 插入数据到 每天/每月/每年线上线下各个校区各个学科的报名人数统计表
INSERT INTO
    edu_online_ads.time_ofline_school_subject
SELECT
    dt_year,
    dt_month,
    dt_day,
    itcast_school_name,
    itcast_subject_name,
    origin_type,
    sign_up_num
FROM
    edu_online_dws.sign_up_count
WHERE
     group_type = 'subject'
  OR group_type = 'school'
  OR group_type = 'ofline';

-- 插入数据到 每天/每月/每年线上线下各个来源渠道的报名人数统计表
INSERT INTO
    edu_online_ads.time_ofline_source
SELECT
    dt_year,
    dt_month,
    dt_day,
    seo_source,
    origin_type,
    sign_up_num
FROM
    edu_online_dws.sign_up_count
WHERE
     group_type = 'source'
  OR group_type = 'ofline';

-- 插入数据到 每天/每月/每年线上线下各个咨询中心的报名人数统计表
INSERT INTO
    edu_online_ads.time_ofline_dept
SELECT
    dt_year,
    dt_month,
    dt_day,
    dept_name,
    origin_type,
    sign_up_num
FROM
    edu_online_dws.sign_up_count
WHERE
     group_type = 'consultation'
  OR group_type = 'ofline';

-- 插入数据到 每天/每月/每年线上线下的意向转报名率统计表
INSERT INTO
    edu_online_ads.time_intention_signup_ratio
SELECT
    dt_year,
    dt_month,
    dt_day,
    origin_type,
    sign_up_num,
    intention_num,
    cast((cast(sign_up_num as decimal(38,4)) / intention_num * 100) as decimal(5,2)) as intention_signup_ratio
FROM
    edu_online_dws.sign_up_count
WHERE
    group_type = 'ofline';

-- 插入数据到 每天/每月/每年线上线下的有效线索报名转换率统计表
INSERT INTO
    edu_online_ads.time_clue_signup_ratio
SELECT
    dt_year,
    dt_month,
    dt_day,
    origin_type,
    sign_up_num,
    cast((cast(sign_up_num as decimal(38,4)) / 269 * 100) as decimal(5,2)) as intention_signup_ratio
FROM
    edu_online_dws.sign_up_count
WHERE
    group_type = 'ofline';



"
"



